﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
using System.Collections;

namespace Fish_Atlantis.SupplierPanel
{
   
	public partial class DataEntry : System.Web.UI.Page
	{
		protected void Page_Load(object sender, EventArgs e)
		{
            //载入页面时自动刷新交易记录
            if (Session["UserID"] != null)
            {
                DataSet dssearch = new DataSet();
                String search = "Select UserName As 顾客,FisheryName As Fishery,Amount As 数量," +
                                "Date As 上传时间,Year(Period) As 交易年份,Month(Period) As 交易月份 " +
                                "From Transaction_Record,Users,Fishery " +
                                "Where Transaction_Record.CustomerID=Users.UserID " +
                                "AND Transaction_Record.FisheryID=Fishery.FisheryID " +
                                "AND SupplierID='" + Session["UserID"].ToString() + "' " +
                                "Order By Period DESC;";
                dssearch = Helper.GetDataSet(search, "SearchTable");

                GridView1.DataSource = dssearch;
                GridView1.DataBind();
            }
            else
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script language='javascript'>alert('您还未登录，请登录后再操作！！');location.href='/Account/Login.aspx';</script>");
            }
            //初始化年份和月份的选项
            ArrayList yearlist = new ArrayList();
            for (int i = 2010; i < 2021; i++)
            {
                yearlist.Add(i+"年");
            }
            for (int i = 0; i < yearlist.Count; i++)
            {
                ChooseYear.Items.Add(new ListItem(yearlist[i].ToString(), i.ToString()));
            }

            ArrayList monthlist = new ArrayList();
            for (int i = 1; i < 13; i++)
            {
                monthlist.Add(i+"月");
            }
            ChooseMonth.DataSource = monthlist;
            for (int i = 0; i < monthlist.Count; i++)
            {
                ChooseMonth.Items.Add(new ListItem(monthlist[i].ToString(), i.ToString()));
            }
		}

        protected void tbSave_Click(object sender, EventArgs e)
        {
            //获取当前用户ID
            if (Session["UserID"] != null)
            {
                string sqlSupplierID = Session["UserID"].ToString();
                try
                {
                    //从User表获取用户名称对应的用户ID
                    string sqlCustmoerID = Helper.ExecuteScalar("select UserID from Users where UserName = '" +
                                                                this.tbCustomer.Text.Trim() + "';");
                    //从Fishery表获取Fishery名称对应的FisheryID
                    string sqlFisheryID = Helper.ExecuteScalar("select FisheryID from Fishery where FisheryName = '" +
                                                               this.ddlFishery.SelectedValue.ToString() + "';");

                    //将数据存入数据库
                    Helper.ExecSQL("insert into Transaction_Record(CustomerID,SupplierID,FisheryID,Amount,Date,Period) values('" +
                                   sqlCustmoerID + "','" + sqlSupplierID + "','" + sqlFisheryID + "','" +
                                   this.tbAmount.Text.Trim() + "','" + DateTime.Now.ToString() + "','" +
                                   (this.ChooseYear.SelectedIndex + 2010) + "-" + (this.ChooseMonth.SelectedIndex + 1) + "-1" + "')");
                    Response.Write("<script language=javascript>alert('" + "保存成功" + "')</script>");

                    //保存成功后刷新报表
                    DataSet dssearch = new DataSet();
                    String search = "Select UserName As 顾客,FisheryName As Fishery,Amount As 数量," +
                                    "Date As 上传时间,Year(Period) As 交易年份,Month(Period) As 交易月份 " +
                                    "From Transaction_Record,Users,Fishery " +
                                    "Where Transaction_Record.CustomerID=Users.UserID " +
                                    "AND Transaction_Record.FisheryID=Fishery.FisheryID " +
                                    "AND SupplierID='" + Session["UserID"].ToString() + "' " +
                                    "Order By Period DESC;";
                    dssearch = Helper.GetDataSet(search, "SearchTable");

                    GridView1.DataSource = dssearch;
                    GridView1.DataBind();

                }
                catch (Exception error)
                {
                    Response.Write("<script language=javascript>alert('" +
                                   "保存失败！顾客名称不存在或者交易数量输入格式有误！" + "')</script>");
                }
            }
            else
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script language='javascript'>alert('您还未登录，请登录后再操作！！');location.href='/Account/Login.aspx';</script>");
            }

            //成功后刷新页面并重新初始化可选项
            ChooseYear.Items.Clear();
            ChooseMonth.Items.Clear();

            //初始化年份和月份的选项
            ArrayList yearlist = new ArrayList();
            for (int i = 2010; i < 2021; i++)
            {
                yearlist.Add(i + "年");
            }
            for (int i = 0; i < yearlist.Count; i++)
            {
                ChooseYear.Items.Add(new ListItem(yearlist[i].ToString(), i.ToString()));
            }

            ArrayList monthlist = new ArrayList();
            for (int i = 1; i < 13; i++)
            {
                monthlist.Add(i + "月");
            }
            ChooseMonth.DataSource = monthlist;
            for (int i = 0; i < monthlist.Count; i++)
            {
                ChooseMonth.Items.Add(new ListItem(monthlist[i].ToString(), i.ToString()));
            }
        }



        //实现了将Excel中的数据写进DataSet中，实现了这一步后，你可以按照自己的需要对获得DataSet进行处理.
        //需要注意的一个问题是，你必须要指定sheetname，即Excel中的哪一个工作单需要导入，否则会出现错误。
        public DataSet ExcelDataSource(string filepath)
        {
            
            string strConn;
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath +
                      ";Extended Properties='Excel 8.0;HDR=No;IMEX=1'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", conn);
            DataSet ds = new DataSet();
            oada.Fill(ds);
            conn.Close();
            return ds;
             
        }
       
        
        protected void btnExcelSave_Click(object sender, EventArgs e)//(已验证函数正确)
        {
            if (Session["UserID"] != null)
            {
                try
                {
                    string FilePath = Server.MapPath("~/") + "Image";
                    HttpFileCollection UserHPF = Request.Files;
                    HttpPostedFile HFC = UserHPF[0];
                    string strFileName = System.IO.Path.GetFileName(HFC.FileName);
                    HFC.SaveAs(FilePath + "//" + strFileName);
                    //找到文件路径
                    string excelpath = Server.MapPath("~/Image/") + strFileName;
                    //获取当前用户ID
                    string sqlSupplierID = Session["UserID"].ToString();

                    //将Excel数据写入Dataset(暂时未写入成功)
                    DataSet ds = ExcelDataSource(excelpath);
                    DataRow[] dr = ds.Tables[0].Select();
                    int rownum = ds.Tables[0].Rows.Count;

                    if (rownum == 0)
                    {
                        Response.Write("<script language=javascript>alert('" + "文件内无数据" + "')</script>");
                        //貌似helper.MessageBox有问题
                    }

                    else
                    {

                        for (int i = 0; i < rownum; i++)
                        {
                            //从User表获取用户名称对应的用户ID
                            string sqlCustmoerID = Helper.ExecuteScalar("select UserID from Users where UserName = '" +
                                                                        ds.Tables[0].Rows[i][2].ToString() + "'");
                            //从Fishery表获取Fishery名称对应的FisheryID
                            string sqlFisheryID = Helper.ExecuteScalar("select FisheryID from Fishery where FisheryName = '" +
                                                                       ds.Tables[0].Rows[i][1].ToString() + "'");

                            //获取转换交易时期
                            System.DateTime excelPeriod = Convert.ToDateTime(ds.Tables[0].Rows[i][4].ToString());
                            string shortPeriod = excelPeriod.ToShortDateString();

                            Helper.ExecSQL("insert into Transaction_Record(CustomerID,SupplierID,FisheryID,Amount,Date,Period) values('" +
                                           sqlCustmoerID + "','" + sqlSupplierID + "','" + sqlFisheryID + "','" +
                                           ds.Tables[0].Rows[i][3].ToString() + "','" + DateTime.Now.ToString() + "','" + shortPeriod + "')");
                        }
                        Response.Write("<script language=javascript>alert('" + "导入成功" + "')</script>");

                        //导入成功后自动刷新数据
                        DataSet dssearch = new DataSet();
                        String search = "Select UserName As 顾客,FisheryName As Fishery,Amount As 数量," +
                                        "Date As 上传时间,Year(Period) As 交易年份,Month(Period) As 交易月份 " +
                                        "From Transaction_Record,Users,Fishery " +
                                        "Where Transaction_Record.CustomerID=Users.UserID " +
                                        "AND Transaction_Record.FisheryID=Fishery.FisheryID " +
                                        "AND SupplierID='" + Session["UserID"].ToString() + "' " +
                                        "Order By Period DESC;";
                        dssearch = Helper.GetDataSet(search, "SearchTable");

                        GridView1.DataSource = dssearch;
                        GridView1.DataBind();

                    }



                }
                catch (Exception error)
                {
                    Response.Write("<script language=javascript>alert('" + "导入失败！请确定Excel数据格式是否正确！" + "')</script>");
                }
            }
            else
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script language='javascript'>alert('您还未登录，请登录后再操作！！');location.href='/Account/Login.aspx';</script>");
            }

            //成功后刷新页面并重新初始化可选项
            ChooseYear.Items.Clear();
            ChooseMonth.Items.Clear();

            //初始化年份和月份的选项
            ArrayList yearlist = new ArrayList();
            for (int i = 2010; i < 2021; i++)
            {
                yearlist.Add(i + "年");
            }
            for (int i = 0; i < yearlist.Count; i++)
            {
                ChooseYear.Items.Add(new ListItem(yearlist[i].ToString(), i.ToString()));
            }

            ArrayList monthlist = new ArrayList();
            for (int i = 1; i < 13; i++)
            {
                monthlist.Add(i + "月");
            }
            ChooseMonth.DataSource = monthlist;
            for (int i = 0; i < monthlist.Count; i++)
            {
                ChooseMonth.Items.Add(new ListItem(monthlist[i].ToString(), i.ToString()));
            }
        }

        protected void Details_Click(object sender, EventArgs e)
        {
            //点击刷新按钮刷新交易记录
            if (Session["UserID"] != null)
            {
                DataSet dssearch = new DataSet();
                String search = "Select UserName As 顾客,FisheryName As Fishery,Amount As 数量," +
                                "Date As 上传时间,Year(Period) As 交易年份,Month(Period) As 交易月份 " +
                                "From Transaction_Record,Users,Fishery " +
                                "Where Transaction_Record.CustomerID=Users.UserID " +
                                "AND Transaction_Record.FisheryID=Fishery.FisheryID " +
                                "AND SupplierID='" + Session["UserID"].ToString() + "' " +
                                "Order By Period DESC;";
                dssearch = Helper.GetDataSet(search, "SearchTable");

                GridView1.DataSource = dssearch;
                GridView1.DataBind();
            }
            else
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script language='javascript'>alert('您还未登录，请登录后再操作！！');location.href='/Account/Login.aspx';</script>");
            }

            //成功后刷新页面并重新初始化可选项
            ChooseYear.Items.Clear();
            ChooseMonth.Items.Clear();

            //初始化年份和月份的选项
            ArrayList yearlist = new ArrayList();
            for (int i = 2010; i < 2021; i++)
            {
                yearlist.Add(i + "年");
            }
            for (int i = 0; i < yearlist.Count; i++)
            {
                ChooseYear.Items.Add(new ListItem(yearlist[i].ToString(), i.ToString()));
            }

            ArrayList monthlist = new ArrayList();
            for (int i = 1; i < 13; i++)
            {
                monthlist.Add(i + "月");
            }
            ChooseMonth.DataSource = monthlist;
            for (int i = 0; i < monthlist.Count; i++)
            {
                ChooseMonth.Items.Add(new ListItem(monthlist[i].ToString(), i.ToString()));
            }
        }

        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            GridView1.DataBind();
        }

	}
}